package com.hellogood.yaohuo.server.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;

import com.hellogood.yaohuo.server.db.DBConnection;
import com.hellogood.yaohuo.server.db.DBHelper;
import com.hellogood.yaohuo.shared.entity.Tz;

public class TzDAO extends DBHelper {
	Logger log = Logger.getLogger(this.getClass());

	@Override
	public List<?> initializeObject(ResultSet rs) throws SQLException {
		List<Tz> list = new ArrayList<Tz>();
		if (rs != null) {
			while (rs.next()) {
				Tz tz = new Tz();
				tz.setOrgCode(rs.getString("OrgCode"));
				tz.setPluCode(rs.getString("pluCode"));
				tz.setBillNo(rs.getString("billNo"));
				tz.setBra(rs.getString("bra"));
				tz.setColor(rs.getString("color"));
				tz.setCounts(rs.getInt("counts"));
				tz.setSize(rs.getString("Csize"));
				tz.setTzDate(rs.getString("tzDate"));
				tz.setTzTime(rs.getString("tzTime"));
				tz.setTzType(rs.getString("tzType"));
				tz.setRemark(rs.getString("remark"));
				tz.setFsJPrice(rs.getBigDecimal("fsJPrice"));
				tz.setFsPrice(rs.getBigDecimal("fsPrice"));
				list.add(tz);
			}
		}
		return list;
	}

	@SuppressWarnings("unchecked")
	public List<Tz> searchTz(String pluCode, String startDate, String endDate, int page, int pageSize) {
		List<Tz> list = new ArrayList<Tz>();
		String sql = "SELECT IDENTITY(INT,1,1) id,* INTO tempdb..temptz FROM tz WHERE pluCode = '" + pluCode.trim()
				+ "' AND CONVERT(VARCHAR(10),CONVERT(DATETIME,tzDate),120) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate
				+ "'),120) AND CONVERT(VARCHAR(10),CONVERT(DATETIME,tzDate),120) <=  CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";

		int start = page * pageSize;

//		Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();
		Statement stmt = null;
		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			stmt = conn.createStatement();
			stmt.execute(sql);
			sql = "SELECT TOP " + pageSize + " * FROM tempdb..temptz WHERE id NOT IN (SELECT TOP " + start
					+ " id FROM tempdb..temptz ORDER BY id) ORDER BY id";
			list = (List<Tz>) super.query(sql);

			sql = "If Exists(SELECT NAME FROM tempdb..sysobjects WHERE NAME='temptz' AND XType='U') DROP TABLE tempdb..temptz";
			stmt.execute(sql);
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
//				ConnectionPool.getInstance().release(conn);
			}
		}
		return list;
	}

	public int getTotalCount(String pluCode, String startDate, String endDate) {
		int number = 0;
		String sql = "SELECT COUNT(*) AS number FROM tz WHERE pluCode = '" + pluCode.trim()
				+ "' AND CONVERT(VARCHAR(10),CONVERT(DATETIME,tzDate),120) >= CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + startDate
				+ "'),120) AND CONVERT(VARCHAR(10),CONVERT(DATETIME,tzDate),120) <=  CONVERT(VARCHAR(10),CONVERT(DATETIME,'" + endDate + "'),120)";

//		Connection conn = ConnectionPool.getInstance().getConnection();
		Connection conn = DBConnection.getInstance().getConnection();

		PreparedStatement ps = null;
		ResultSet rs = null;

		try {
			ps = conn.prepareStatement(sql);
			rs = ps.executeQuery();
			while (rs.next()) {
				number = rs.getInt("number");
			}
		} catch (Exception ex) {
			ex.printStackTrace();
			log.error(ex.getMessage());
		} finally {
			if (rs != null) {
				try {
					rs.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (ps != null) {
				try {
					ps.close();
				} catch (SQLException e) {
					e.printStackTrace();
				}
			}
			if (conn != null) {
//				ConnectionPool.getInstance().release(conn);
			}
		}
		return number;
	}
}
